package br.com.hmm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.hmm.businesslogic.CategoriaService;
import br.com.hmm.entidade.ItensCategoria;

public class ItemCategoriaDAO {
	
	private Connection conn = null;
	private static final String INSERT_ITEM_CATEGORIA = "INSERT INTO ITENS_CATEGORIA(ITN_ID,ITN_NOME,ITN_DESCRICAO, CATEGORIA_CAT_ID) VALUES(NULL,?,?,?); ";
	private static final String UPDATE_ITEM_CATEGORIA = "UPDATE ITENS_CATEGORIA SET ITN_NOME = ? , ITN_DESCRICAO =?, CATEGORIA_CAT_ID = ? WHERE ITN_ID = ? ;";
	private static final String DELETE_ITEM_CATEGORIA = "DELETE FROM ITENS_CATEGORIA WHERE ITN_ID = ? ;";
	private static final String EXISTE_ITEM_CATEGORIA = "SELECT ITN_ID, ITN_NOME FROM ITENS_CATEGORIA WHERE ITN_NOME = ? ;";
	private static final String FIND_BY_ID			  = "SELECT ITN_ID, ITN_NOME, ITN_DESCRICAO, CATEGORIA_CAT_ID FROM ITENS_CATEGORIA WHERE ITN_ID = ? ;";
	private static final String LISTA_ITEM_CATEGORIA = "SELECT i.ITN_ID, i.ITN_NOME, i.ITN_DESCRICAO, i.CATEGORIA_CAT_ID, c.CAT_ID FROM ITENS_CATEGORIA i, CATEGORIA c WHERE i.CATEGORIA_CAT_ID = c.CAT_ID ORDER BY ITN_NOME ;";
	private static final String LISTA_ITEM			 = "SELECT i.ITN_ID, i.ITN_NOME, i.ITN_DESCRICAO, i.CATEGORIA_CAT_ID FROM ITENS_CATEGORIA i WHERE i.CATEGORIA_CAT_ID = ? ORDER BY ITN_NOME ;";
	public ItemCategoriaDAO(){
		
	}
	
	/**
	 * 
	 * @param itensCategoria
	 * @return
	 */
	public boolean inserir(ItensCategoria itensCategoria) {
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = conn.prepareStatement(INSERT_ITEM_CATEGORIA);
			stmt.setString(1, itensCategoria.getNome());
			stmt.setString(2, itensCategoria.getDescricao());
			stmt.setInt(3, itensCategoria.getCategoria().getId());
			stmt.execute();
			if(stmt.getUpdateCount() > 0){
				return true;
			}

		} catch (Exception e) {
			return false;
		} finally {
			DAOUtil.close(this.conn, stmt);
		}

		return false;
	}
	
	/**
	 * 
	 * @param itensCategoria
	 * @return
	 */
	public boolean atualizar(ItensCategoria itensCategoria) {
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = conn.prepareStatement(UPDATE_ITEM_CATEGORIA);
			stmt.setString(1, itensCategoria.getNome());
			stmt.setString(2, itensCategoria.getDescricao());
			stmt.setInt(3, itensCategoria.getCategoria().getId());
			stmt.setInt(4, itensCategoria.getId());
			if (stmt.executeUpdate() > 0) {
				return true;
			}

		} catch (SQLException exp) {
			return false;
		} finally {
			DAOUtil.close(this.conn, stmt);
		}

		return false;
	}
	
	/**
	 * 
	 * @param itensCategoria
	 * @return
	 */
	public boolean delete(ItensCategoria itensCategoria) {
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = conn.prepareStatement(DELETE_ITEM_CATEGORIA);
			stmt.setInt(1, itensCategoria.getId());
			stmt.execute();
			if(stmt.getUpdateCount()> 0){
				
				return true;
			}

		} catch (SQLException exp) {
			return false;
		} finally {
			DAOUtil.close(this.conn, stmt);
		}

		return false;
	}
	
	/**
	 * 
	 * @param itensCategoria
	 * @return
	 */
	public boolean existeItemCategoria(ItensCategoria itensCategoria) {
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = conn.prepareStatement(EXISTE_ITEM_CATEGORIA);
			stmt.setString(1, itensCategoria.getNome());
			stmt.execute();
			if(stmt.getResultSet().first() == true){
				return true;
			}
		} catch (SQLException exp) {
			return false;
		} finally {
			DAOUtil.close(this.conn, stmt);
		}
		return false;
	}
	
	/**
	 * 
	 * @return
	 */
	public List<ItensCategoria> list() {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<ItensCategoria> listaItensCategoria = new ArrayList<ItensCategoria>();
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = conn.prepareStatement(LISTA_ITEM_CATEGORIA);
			rs = stmt.executeQuery();
			while (rs.next()) {
				ItensCategoria ic = new ItensCategoria();
				ic.setId(rs.getInt("ITN_ID"));
				ic.setNome(rs.getString("ITN_NOME"));
				ic.setDescricao(rs.getString("ITN_DESCRICAO"));
				ic.setCategoria(CategoriaService.findById(rs.getInt("CATEGORIA_CAT_ID")));
				listaItensCategoria.add(ic);
			}
			if(!listaItensCategoria.isEmpty()){
				return listaItensCategoria;
			}
			
		} catch (SQLException exp) {
			return null;
		} finally {
			DAOUtil.close(this.conn, stmt, rs);
		}
		
		return null;
	}
	
	public List<ItensCategoria> listByCategoria(int id) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<ItensCategoria> listaItensCategoria = new ArrayList<ItensCategoria>();
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = conn.prepareStatement(LISTA_ITEM);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				ItensCategoria ic = new ItensCategoria();
				ic.setId(rs.getInt("ITN_ID"));
				ic.setNome(rs.getString("ITN_NOME"));
				ic.setDescricao(rs.getString("ITN_DESCRICAO"));
				ic.setCategoria(CategoriaService.findById(rs.getInt("CATEGORIA_CAT_ID")));
				listaItensCategoria.add(ic);
			}
			if(!listaItensCategoria.isEmpty()){
				return listaItensCategoria;
			}
			
		} catch (Exception exp) {
			throw new SQLException(exp.getMessage());
		} finally {
			DAOUtil.close(this.conn, stmt, rs);
		}
		
		return null;
	}
	
	
	public ItensCategoria findById(int id) throws SQLException{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = conn.prepareStatement(FIND_BY_ID);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.first() == true) {
				ItensCategoria i = new ItensCategoria();
				i.setId(rs.getInt(1));
				i.setNome(rs.getString(2));
				i.setDescricao(rs.getString(3));
				i.setCategoria(CategoriaService.findById(rs.getInt(4)));
				return i;
			}
		}catch (Exception e) {
			throw new SQLException(e.getMessage());
		}finally {
			DAOUtil.close(this.conn, stmt, rs);
		}
		return null;
	}
	
}
